﻿using Dapper;
using Microsoft.AspNetCore.Http;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using WPay.Infrastructure.Unity;
using Z.Dapper.Plus;

namespace WPay.Infrastructure.Repository.DapperCore
{
    /// <summary>
    /// Dapper 数据库上下文 DbContext 
    /// 作用是调用Dapper中操作数据库的方法
    /// </summary>
    public class DapperDbContext
    {
        /// <summary>
        /// 读写数据库连接对象
        /// </summary>
        protected IDbConnection DbRW;
        /// <summary>
        /// 只读数据库连接对象
        /// </summary>
        protected IDbConnection DbRO;


        #region 通过SQL语句执行数据库操作
        /// <summary>
        /// 查询单条记录
        /// </summary>
        /// <typeparam name="T">泛型T（实体类型）</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数对象，格式可为:[New {ParamName=ParamValue}]</param>
        /// <returns></returns>
        protected T QueryBySql<T>(string sql, object param = null)
        {
            return DbRO.QueryFirstOrDefault<T>(sql, param);
        }
        /// <summary>
        /// 查询多条记录的实体泛型集合
        /// </summary>
        /// <typeparam name="T">泛型T（实体类型）</typeparam>
        /// <param name="sql">sdmap对象名,如 Cpn.getCpn </param>
        /// <param name="param">参数对象，格式可为:[new {ParamName=ParamValue}]</param>
        /// <param name="buffered">是否缓存，默认：true</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行语句类型</param>
        /// <returns>返回实体类型List集合</returns>
        protected List<T> QueryListBySql<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            return DbRW.Query<T>(sql, param, null, buffered, commandTimeout, commandType).AsList<T>();
        }

        /// <summary>
        /// 查询第一行第一列数据结果
        /// 如：取Count技术、Sum求和等
        /// </summary>
        /// <typeparam name="T">返回数据类型</typeparam>
        /// <param name="sql">sdmap对象名,如 Cpn.getCpn </param>
        /// <param name="param">参数对象，格式可为:[new {ParamName=ParamValue}]</param>
        /// <returns>返回第一行第一列数据结果</returns>
        protected T QueryScalarBySql<T>(string sql, object param = null)
        {
            return DbRO.ExecuteScalar<T>(sql, param);
        }
        /// <summary>
        /// 执行单条增删改语句（无统一事务控制）
        /// </summary>
        /// <param name="sql">sdmap对象名,如 Cpn.getCpn </param>
        /// <param name="param">参数对象，格式可为:[new {ParamName=ParamValue}]</param>
        /// <returns>返回语句执行影响数据条数</returns>
        protected int ExecuteBySql(string sql, object param)
        {
            return DbRW.Execute(sql, param);
        }
        /// <summary>
        /// 执行单条增删改语句（统一事务控制）
        /// </summary>
        /// <param name="sql">sdmap对象名,如 Cpn.getCpn </param>
        /// <param name="param">参数对象，格式可为:[new {ParamName=ParamValue}]</param>
        /// <param name="transaction">事务对象</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行语句类型</param>
        /// <returns>返回语句执行影响数据条数</returns>
        protected int ExecuteBySql(string sql, object param, IDbTransaction transaction, int? commandTimeout = null, CommandType? commandType = null)
        {
            return DbRW.Execute(sql, param, transaction, commandTimeout, commandType);
        }
        #endregion


        #region 批量插入
        /// <summary>
        /// 执行批量插入数据集合
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="listT">批量插入对象集合</param>
        /// <returns>返回语句执行影响数据条数</returns>
        protected int BulkInsert<T>(List<T> t)
        {
            return this.BulkInsert(t.ToArray());
        }
        /// <summary>
        /// 执行批量插入数据集合
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="listT">批量插入对象集合</param>
        /// <returns>返回语句执行影响数据条数</returns>
        protected int BulkInsert<T>(params T[] t)
        {
            var lst = DbRW.BulkInsert<T>(t);
            int count = lst.Current.Count();
            return count;
        }
        /// <summary>
        /// 执行批量插入数据集合
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="listT">批量插入对象集合</param>
        /// <returns>返回语句执行影响数据条数</returns>
        protected int BulkInsert<T>(string sql, List<T> listT)
        {
            return this.BulkInsert<T>(sql, listT.ToArray());
        }
        /// <summary>
        /// 执行批量插入数据集合
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="listT">批量插入对象集合</param>
        /// <returns>返回语句执行影响数据条数</returns>
        protected int BulkInsert<T>(string sql, T[] items)
        {
            var lst = DbRW.BulkInsert<T>(sql, items);
            int count = lst.Current.Count();
            return count;
        }
        #endregion



        #region SQL语句组装
        /// <summary>
        /// 查询SQL 语句WHERE条件组装
        /// </summary>
        /// <param name="_param">查询条件字典，其中key必须使用字段名</param>
        /// <param name="_pageIndex">查询页码，从1开始</param>
        /// <param name="_pageSize">每页查询数据条数</param>
        /// <param name="_orderBy">排序列名，若要降序请用列名+" DESC"</param>
        /// <returns>返回sql语句where后所有语句</returns>
        protected virtual string GetQueryConditions(Hashtable _param, int _pageIndex = 0, int _pageSize = 0, string _orderBy = null)
        {
            string conditionStr = "";
            //循环where处理条件 
            foreach (var proName in _param.Keys)
            {
                conditionStr += (" and " + proName + "=@" + proName);
            }
            //拼接排序
            if (!string.IsNullOrWhiteSpace(_orderBy))
            {
                conditionStr += (" order by " + _orderBy);
            }
            //处理分页
            if (_pageIndex > 0)
            {
                if (_pageSize <= 0)
                    _pageSize = 30;
                //计算分页条件
                int limitStart = (_pageIndex - 1) * _pageSize;
                conditionStr += " limit " + limitStart + "," + _pageSize.ToString();
            }
            return conditionStr;
        }


        /// <summary>
        /// 查询SQL 语句WHERE条件组装
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="t">实体参数对象</param>
        /// <param name="_pageIndex">查询页码，从1开始</param>
        /// <param name="_pageSize">每页查询数据条数</param>
        /// <param name="_orderBy">排序列名，若要降序请用列名+" DESC"</param>
        /// <returns>返回sql语句where后所有语句</returns>
        protected virtual string GetQueryConditions<T>(T t, int _pageIndex = 0, int _pageSize = 0, string _orderBy = null)
        {
            string conditionStr = "";
            //获取 实体类 类型对象
            Type tType = typeof(T);
            //获取 实体类 所有的 公有属性 
            List<PropertyInfo> proInfos = tType.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
            //将 实体属性 中要修改的属性名 添加到 字典集合中 键：属性名  值：属性对象 
            proInfos.ForEach(p =>
            {
                //从参数t中取出 要修改的值
                var newValue = p.GetValue(t, null);
                if (newValue != null && newValue != default)
                {
                    conditionStr += " and " + p.Name + "=@" + p.Name;
                }
            });
            //拼接排序
            if (!string.IsNullOrWhiteSpace(_orderBy))
            {
                conditionStr += (" order by " + _orderBy);
            }
            //处理分页
            if (_pageIndex > 0)
            {
                if (_pageSize <= 0)
                    _pageSize = 30;
                int limitStart = (_pageIndex - 1) * _pageSize;
                conditionStr += " limit " + limitStart + "," + _pageSize.ToString();
            }
            return conditionStr;
        }

        /// <summary>
        /// 获取Update语句后Set部分
        /// </summary>
        /// <param name="_updateProNames">要修改的字段</param>
        /// <returns>返回Update语句Set后要修改的字段部分sql语句</returns>
        protected virtual string GetUpdateSet(params string[] _updateProNames)
        {
            if (_updateProNames.Length == 0)
                return "";
            string updateStr = "";
            //循环拼接要修改的字段名 
            foreach (string proName in _updateProNames)
            {
                updateStr += (proName + "=@" + proName + ",");
            }
            updateStr = updateStr.Substring(0, updateStr.Length - 1) + " where 1=1";
            return updateStr;
        }
        #endregion

        ///// <summary>
        ///// 同时查询多张表数据（高级查询）
        ///// "select *from K_City;select *from K_Area";
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <returns></returns>
        //public SqlMapper.GridReader QueryMultiple(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        //{
        //    return Db.QueryMultiple(sql, param, transaction, commandTimeout, commandType);
        //}
        //public Task<SqlMapper.GridReader> QueryMultipleAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        //{
        //    return Db.QueryMultipleAsync(sql, param, transaction, commandTimeout, commandType);
        //}

        /**

                #region 通过sdmap语句执行数据库操作
                /// <summary>
                /// 查询单条记录
                /// </summary>
                /// <typeparam name="T">泛型T（实体类型）</typeparam>
                /// <param name="sqlMapName">sdmap对象名,如 Cpn.getCpn </param>
                /// <param name="param">参数对象，格式可为:[new {ParamName=ParamValue}]</param>
                /// <returns>返回单条实体类型</returns>
                public T QueryByMap<T>(string sqlMapName, object param = null)
                {
                    return Db.QueryFirstOrDefaultByMap<T>(sqlMapName, param);
                }
                /// <summary>
                /// 查询多条记录的实体泛型集合
                /// </summary>
                /// <typeparam name="T">泛型T（实体类型）</typeparam>
                /// <param name="sqlMapName">sdmap对象名,如 Cpn.getCpn </param>
                /// <param name="param">参数对象，格式可为:[new {ParamName=ParamValue}]</param>
                /// <param name="buffered">是否缓存，默认：true</param>
                /// <param name="commandTimeout">超时时间（秒）</param>
                /// <param name="commandType">执行语句类型</param>
                /// <returns>返回实体类型List集合</returns>
                public List<T> QueryListByMap<T>(string sqlMapName, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
                {
                    return Db.QueryByMap<T>(sqlMapName, param, null, buffered, commandTimeout, commandType).AsList<T>();
                }
                /// <summary>
                /// 查询第一行第一列数据结果
                /// 如：取Count技术、Sum求和等
                /// </summary>
                /// <typeparam name="T">返回数据类型</typeparam>
                /// <param name="sqlMapName">sdmap对象名,如 Cpn.getCpn </param>
                /// <param name="param">参数对象，格式可为:[new {ParamName=ParamValue}]</param>
                /// <returns>返回第一行第一列数据结果</returns>
                public T ExecuteScalarByMap<T>(string sqlMapName, object param = null)
                {
                    return Db.ExecuteScalarByMap<T>(sqlMapName, param);
                }
                /// <summary>
                /// 执行单条增删改语句（无统一事务控制）
                /// </summary>
                /// <param name="sqlMapName">sdmap对象名,如 Cpn.getCpn </param>
                /// <param name="param">参数对象，格式可为:[new {ParamName=ParamValue}]</param>
                /// <returns>返回语句执行影响数据条数</returns>
                public int ExecuteByMap(string sqlMapName, object param)
                {
                    return Db.ExecuteByMap(sqlMapName, param);
                }
                /// <summary>
                /// 执行单条增删改语句（统一事务控制）
                /// </summary>
                /// <param name="sqlMapName">sdmap对象名,如 Cpn.getCpn </param>
                /// <param name="param">参数对象，格式可为:[new {ParamName=ParamValue}]</param>
                /// <param name="transaction">事务对象</param>
                /// <param name="commandTimeout">超时时间（秒）</param>
                /// <param name="commandType">执行语句类型</param>
                /// <returns>返回语句执行影响数据条数</returns>
                public int ExecuteByMap(string sqlMapName, object param, IDbTransaction transaction, int? commandTimeout = null, CommandType? commandType = null)
                {
                    return Db.ExecuteByMap(sqlMapName, param, transaction, commandTimeout, commandType);
                }

                #endregion

                private string EmitSql(string statementName, object parameterObject)
                {
                    string sql = DbConnectionExtensions.EmitSql(statementName, parameterObject);
                    return sql;
                }
            */
    }
}
